package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import bean.AlumnoVO;

public class AlumnoDAO {

private static AlumnoDAO ins;
	
	private AlumnoDAO(){
		
	}
	
	public static AlumnoDAO getInstancia(){
		
		if(ins == null)
			ins = new AlumnoDAO();
		return ins;
		
	}
	
	public AlumnoVO obtener(AlumnoVO arg){
		
		AlumnoVO a = null;
		String sql = "Select * from Alumno Where legajo = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, arg.getLegajo());
			rs = stmt.executeQuery();
			if(rs.next()){
				a = new AlumnoVO();
				a.setLegajo(rs.getInt(1));
				a.setNombre(rs.getString(2));
				a.setEstado(rs.getString(3));
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a ;
		
	}
	
	
	public Vector<AlumnoVO> obtenerTodos(){
		
        Vector<AlumnoVO> alumnos = new Vector<AlumnoVO>();
		String sql = "Select * from Alumno";
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				AlumnoVO a = new AlumnoVO();
				a.setLegajo(rs.getInt(1));
				a.setNombre(rs.getString(2));
				a.setEstado(rs.getString(3));
				alumnos.add(a);
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return alumnos;
		
	}
	
	
	public void guardar(AlumnoVO a){
		
		String sql = "Insert into Alumno values (?,?,?)";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, a.getLegajo());
			stmt.setString(2, a.getNombre());
			stmt.setString(3, a.getEstado());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	
	public void actualizar(AlumnoVO a){
		
		String sql = "Update Alumno Set nombre = ?, estado = ? Where legajo =? ";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, a.getNombre());
			stmt.setString(2, a.getEstado());
			stmt.setInt(3, a.getLegajo());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	public void eliminar(AlumnoVO arg){
		
		String sql = "Delete Alumno Where legajo = ?";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, arg.getLegajo());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	
	
	
}